﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IOA.MES.Client.Framing
{
    public class DBHelper
    {
        //从配置文件中读取连接字符串
        private static string connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
        //从配置文件中读取连接字符串 表名，列名
        public static string TableName = ConfigurationManager.ConnectionStrings["TableName"].ConnectionString;
        public static string ColumnName = ConfigurationManager.ConnectionStrings["ColumnName"].ConnectionString;
       
        /// <summary>
        /// 根据sql语句的不同返回不同的DataTable,
        //  相对于数据集来说,占用内存小,并且不用每次都清空数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable ExecuteTable(String sql)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connectionString))
            {
                DataTable table = new DataTable();
                adapter.Fill(table);
                return table;
            }
        }
        /// <summary>
        /// 根据SQL语句的不同,执行查询操作,返回不同的SqlDataReader结果集
        /// </summary>
        public static SqlDataReader ExecuteReader(String sql)
        {
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Connection = new SqlConnection(connectionString);
                command.Connection.Open();
                return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
        }
        /// <summary>
        /// 根据SQL语句的不同,执行增删改操作,返回数据库影响的记录
        /// </summary>
        public static int ExecuteNonQuery(String sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                return command.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 根据SQL语句的不同,执行查询单个值的操作,并返回结果集中的第一行第一列
        /// </summary>
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                return command.ExecuteScalar();
            }
        }
        /// <summary>
        /// 根据SQL语句的不同,执行查询单个值的操作,并返回结果集中的第一行第一列
        /// </summary>
        public static object ExecuteBarCodeScalar(String barCode)
        {
            String sql = "SELECT COUNT(*) FROM @TableName WHERE @ColumnName = @Value";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.Add(new SqlParameter("TableName", TableName));
                command.Parameters.Add(new SqlParameter("ColumnName", ColumnName));
                command.Parameters.Add(new SqlParameter("Value", barCode));
                return command.ExecuteScalar();
            }
        }
        /// <summary>
        /// 根据SQL语句的不同,执行增删改操作,返回数据库影响的记录
        /// </summary>
        public static int OlExecuteNonQuery(string connectionString, string sql)
        {
            using (var conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                return cmd.ExecuteNonQuery();
            }
        }
    }
}
